/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.myapp.dbdao;

import com.myapp.bo.Diccionario;
import com.myapp.bo.Eventor;
import com.myapp.bo.Extensiontabs;
import com.myapp.bo.LevelOneBO;
import com.myapp.bo.LevelTwoBO;
import com.myapp.struts.form.ReportForm;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author NgoTienSy
 */
public class DesInvestDBDAO {

    public Connection getConnection() {
        String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
        String username = "sa";
        String pass = "12345";
        String url = "jdbc:sqlserver://localhost:1433;databasename=vietnam";
        try {
            Class.forName(driverName).newInstance();
            return DriverManager.getConnection(url, username, pass);
        } catch (SQLException ex) {
            Logger.getLogger(DesInvestDBDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            Logger.getLogger(DesInvestDBDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            Logger.getLogger(DesInvestDBDAO.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DesInvestDBDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    /**
     *
     */
    public static void destroy(Connection connection) throws SQLException {
        if (connection != null && !(connection.isClosed())) {
            connection.close();
        }
    }

    /**
     * lấy về danh sách loại thiên tai
     * @return
     */
    public List<Eventor> getAllEventor() {
        List<Eventor> lst = new ArrayList<Eventor>();
        Eventor item;
        String sql = "select * from eventos";
        Connection conn = getConnection();
        try {
            PreparedStatement pre = conn.prepareStatement(sql);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                item = new Eventor();
                item.setSerial(rs.getInt("serial"));
                item.setNumbre(rs.getString("nombre"));
                item.setNumbreEng(rs.getString("nombre_en"));
                item.setDescription(rs.getString("descripcion"));
                lst.add(item);
            }

        } catch (SQLException ex) {
            Logger.getLogger(DesInvestDBDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lst;
    }

    /**
     * lấy về danh sách tỉnh thành
     * mức level 1
     * @return
     */
    public List<LevelOneBO> getAllLevelOne() {
        List<LevelOneBO> lst = new ArrayList<LevelOneBO>();
        LevelOneBO item;
        String sql = "select * from lev0";
        Connection conn = getConnection();
        try {
            PreparedStatement pre = conn.prepareStatement(sql);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                item = new LevelOneBO();
                item.setCode(rs.getString("lev0_cod"));
                item.setName(rs.getString("lev0_name"));
                item.setNameEng(rs.getString("lev0_name_en"));
                lst.add(item);
            }

        } catch (SQLException ex) {
            Logger.getLogger(DesInvestDBDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lst;
    }

    /**
     * lấy về danh sách huyện theo tỉnh
     * @param levelOneCode
     * @return
     */
    public List<LevelTwoBO> getLevelTwoByLevelOne(String levelOneCode) {
        List<LevelTwoBO> lst = new ArrayList<LevelTwoBO>();
        LevelTwoBO item;
        String sql = "select * from lev1 where lev1_lev0 = ? ";
        Connection conn = getConnection();
        try {
            PreparedStatement pre = conn.prepareStatement(sql);
            pre.setString(1, levelOneCode);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                item = new LevelTwoBO();
                item.setCode(rs.getString("lev1_cod"));
                item.setName(rs.getString("lev1_name"));
                item.setNameEng(rs.getString("lev1_name_en"));
                item.setLevelOneCode(rs.getString("lev1_lev0"));
                lst.add(item);
            }

        } catch (SQLException ex) {
            Logger.getLogger(DesInvestDBDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lst;
    }

    /**
     * lấy về danh sách các tab chính trong chương trình
     * @return
     */
    public List<Extensiontabs> getAllExtensionTab(ReportForm frm) {
        List<Extensiontabs> lst = new ArrayList<Extensiontabs>();
        Extensiontabs item;
        String sql = " select * from Extensiontabs order by ntab";
        Connection conn = getConnection();
        try {
            PreparedStatement pre = conn.prepareStatement(sql);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                item = new Extensiontabs();
                item.setNtab(rs.getInt("ntab"));
                item.setNsort(rs.getInt("nsort"));
                item.setSvalue(rs.getString("svalue"));
                item.setSvalueEn(rs.getString("svalue_en"));
                List<Diccionario> allDiccionarioOfTab = getAllDiccionarioOfTab(item.getNtab(), frm);
                item.setLstDiccionario(allDiccionarioOfTab);
                lst.add(item);
            }

        } catch (SQLException ex) {
            Logger.getLogger(DesInvestDBDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lst;
    }

    /**
     * lấy về danh sách các truong trong tab tương ứng
     * @return
     */
    public List<Diccionario> getAllDiccionarioOfTab(int tabNumber, ReportForm frm) {
        List<Diccionario> lst = new ArrayList<Diccionario>();
        Diccionario item;
        String sql = " select * from diccionario where tabnumber = ? order by nombre_campo ";
        Connection conn = getConnection();
        try {
            PreparedStatement pre = conn.prepareStatement(sql);
            pre.setInt(1, tabNumber);
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                item = new Diccionario();
                item.setNombre_campo(rs.getString("nombre_campo"));
                item.setLabel_campo_en(rs.getString("label_campo_en"));
                item.setLabel_campo(rs.getString("label_campo"));
                item.setDescripcion_campo(rs.getString("descripcion_campo"));
                item.setTabnumber(rs.getInt("tabnumber"));
                if (!item.getNombre_campo().equals("clave_ext")) {
                    item.setNumber(getLstFichas(frm, item.getNombre_campo()));
                }
                lst.add(item);
            }

        } catch (SQLException ex) {
            Logger.getLogger(DesInvestDBDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lst;
    }
//    fichas

    public Long getLstFichas(ReportForm frm, String column) {
        StringBuilder str = new StringBuilder();
        str.append(" select sum(").append(column).append(") from fichas f left join extension ex ");
        str.append(" on f.clave = ex.clave  where 1 =1 ");
        str.append(" and level0 = ? and evento = ?");
        PreparedStatement pre;
        try {
            pre = getConnection().prepareStatement(str.toString());
            pre.setString(1, frm.getProvince());
            pre.setString(2, frm.getName());
            ResultSet rs = pre.executeQuery();
            return rs.getLong(column);

        } catch (SQLException ex) {
            Logger.getLogger(DesInvestDBDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public ResultSet getLstFichas() {
        String sql = "select * from fichas where evento like ?";
        PreparedStatement pre;
        try {
            pre = getConnection().prepareStatement(sql);
            pre.setString(1, "Lũ");
            return pre.executeQuery();
        } catch (SQLException ex) {
            Logger.getLogger(DesInvestDBDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public static void main(String[] args) throws SQLException {
        DesInvestDBDAO dbDao = new DesInvestDBDAO();
        ResultSet rs = dbDao.getLstFichas();
        while (rs.next()) {
            System.out.println(rs.getString("evento"));
        }
//        List<Diccionario> allDiccionarioOfTab = dbDao.getAllDiccionarioOfTab(1);
//        for (Diccionario diccionario : allDiccionarioOfTab) {
//            System.out.println(diccionario.getNombre_campo() + "-" + diccionario.getLabel_campo() + "---"+diccionario.getUnit());
//        }
//        System.out.println(allDiccionarioOfTab.size());
//        List<Extensiontabs> allExtensionTab = dbDao.getAllExtensionTab();
//        for (Extensiontabs extensiontabs : allExtensionTab) {
//            System.out.println(extensiontabs.getNtab());
//        }

    }
}
